﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using E4RPTools;
using TakeAway.DataSet;

namespace TakeAway
{
    public class PhcDatabase
    {
        private static string UserName(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	top 1 username
	                from	us 
	                where	usercode = system_user";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query);
        }

        private static string SessionStamp(SqlTransaction sqlTransaction, string cashStamp)
        {
            const string query =
                @"  select	ssstamp
	                from	ss
	                where	cxstamp = @CashStamp
			                and fechada = 0
			                and ausername = @UserName";
            var userName = UserName(sqlTransaction);
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("CashStamp", cashStamp), new SqlParameter("UserName", userName));
        }

        private static string GenerateStamp(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	iniciais + 
			                substring(convert(varchar, getutcdate(), 126), 3, 2) +
			                substring(convert(varchar, getutcdate(), 126), 6, 2) +
			                substring(convert(varchar, getutcdate(), 126), 9, 2) +
			                substring(convert(varchar, getutcdate(), 126), 12, 2) +
			                substring(convert(varchar, getutcdate(), 126), 15, 2) +
			                substring(convert(varchar, getutcdate(), 126), 18, 6) as stamp
	                from	us 
	                where	usercode = system_user";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query);
        }

        private static DateTime GetDateWithoutTime(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select convert( datetime, convert(varchar, getdate(), 104), 104)";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, DateTime.Now.Date, query);
        }

        private static string GetTime(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	convert(varchar, getdate(), 108)";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query);
        }

        private static int UserNumber(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	top 1 userno
	                from	us 
	                where	usercode = system_user";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query);
        }

        private static string UserInitials(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	top 1 iniciais
	                from	us 
	                where	usercode = system_user";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query);
        }

        private static void GetCashData(SqlTransaction sqlTransaction, string cashStamp, out string posSite, out string posName, out int posNumber, out string cashUserName)
        {
            var table = new DataTable();
            const string query =
                @"  select	site, pnome, pno, ausername
                    from	cx
                    where	cxstamp = @CashStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("CashStamp", cashStamp));
            var row = table.Rows[0];
            posSite = row["site"].ToString();
            posName = row["pnome"].ToString();
            posNumber = Convert.ToInt32(row["pno"]);
            cashUserName = row["ausername"].ToString();
        }

        private static void InsertSession(SqlTransaction sqlTransaction, string sessionStamp, string posSite, string posName, int posNumber, string cashStamp, string cashUserName, 
            string userName, int userNumber, DateTime date, string time, string userInitials)
        {
            const string query =
                @"  insert 
                    into    ss (
                            ssstamp, site, pnome, pno, cxstamp, cxusername, ausername, auserno, dabrir,
                            habrir, fechada, ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora)
                    values	(
                            @SessionStamp, @PosSite, @PosName, @PosNumber, @CashStamp, @CashUserName, @UserName, 
                            @UserNumber, @Date, @Time, 0, @UserInitials, @Date, @Time, @UserInitials, @Date, @Time)";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SessionStamp", sessionStamp),
                    new SqlParameter("PosSite", posSite), new SqlParameter("PosName", posName),
                    new SqlParameter("PosNumber", posNumber), new SqlParameter("CashStamp", cashStamp),
                    new SqlParameter("CashUserName", cashUserName), new SqlParameter("UserName", userName),
                    new SqlParameter("UserNumber", userNumber), new SqlParameter("Date", date),
                    new SqlParameter("Time", time), new SqlParameter("UserInitials", userInitials));
        }

        private static long GetNextClientNumber(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	max(no) + 1
	                from	cl";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<long>(sqlTransaction, 0, query);
        }

        private static long InsertClient(SqlTransaction sqlTransaction, ClienteDataSet.ClienteRow client)
        {
            var number = GetNextClientNumber(sqlTransaction);
            var stamp = GenerateStamp(sqlTransaction);
            var userInitials = UserInitials(sqlTransaction);
            var date = GetDateWithoutTime(sqlTransaction);
            var time = GetTime(sqlTransaction);
            const string query =
                @"  insert
                    into	cl (
		                    clstamp, nome, no, telefone, c1tele, c2tele, c3tele, ncont, email, nascimento, estab, tlmvl, 
                            local, u_Morada, codpost, zona, u_Observa, morada, 
                            u_LocalF, u_MoradaF, u_CodPostF, u_ZonaF, u_ObservaF,
                            u_LocalE, u_MoradaE, u_CodPostE, u_ZonaE, u_ObservaE,
                            ousrinis, ousrdata, ousrhora, usrinis, usrdata, usrhora, preco, codfornecedor) 
	                values	(
		                    @Stamp,	@Nome, @Numero, @Telefone, @Telemovel2, @Telemovel3, @Telemovel1, @Contribuinte, @Email, @DataNascimento, 0, @Telemovel1, 
                            @CidadeCasa, @MoradaCasa, @CodigoPostalCasa, @ZonaCasa, @ObservacoesCasa, @MoradaCasa, 
                            @CidadeFerias, @MoradaFerias, @CodigoPostalFerias, @ZonaFerias, @ObservacoesFerias,
                            @CidadeEmprego, @MoradaEmprego,@CodigoPostalEmprego, @ZonaEmprego, @ObservacoesEmprego,
                            @UserInitials, @Date, @Time, @UserInitials, @Date, @Time, 1, 0)";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("Stamp", stamp),
                                     new SqlParameter("Nome", client.Nome),
                                     new SqlParameter("Numero", number),
                                     new SqlParameter("Telefone", client.IsTelefoneNull() ? Convert.DBNull : client.Telefone),
                                     new SqlParameter("Telemovel1", client.IsTelemovel1Null() ? Convert.DBNull : client.Telemovel1),
                                     new SqlParameter("Telemovel2", client.IsTelemovel2Null() ? Convert.DBNull : client.Telemovel2),
                                     new SqlParameter("Telemovel3", client.IsTelemovel3Null() ? Convert.DBNull : client.Telemovel3),
                                     new SqlParameter("Contribuinte", client.IsContribuinteNull() ? Convert.DBNull : client.Contribuinte),
                                     new SqlParameter("Email", client.IsEmailNull() ? Convert.DBNull : client.Email),
                                     new SqlParameter("DataNascimento", client.IsDataNascimentoNull() || client.DataNascimento == DateTime.MinValue ? new DateTime(1900, 3, 1) : client.DataNascimento),
                                     new SqlParameter("CidadeCasa", client.IsCidadeCasaNull() ? "" : client.CidadeCasa),
                                     new SqlParameter("MoradaCasa", client.IsMoradaCasaNull() ? "" : client.MoradaCasa),
                                     new SqlParameter("CodigoPostalCasa", client.IsCodigoPostalCasaNull() ? "" : client.CodigoPostalCasa),
                                     new SqlParameter("ZonaCasa", client.IsZonaCasaNull() ? "" : client.ZonaCasa),
                                     new SqlParameter("ObservacoesCasa", client.IsObservacoesCasaNull() ? "" : client.ObservacoesCasa),                                     
                                     new SqlParameter("CidadeFerias", client.IsCidadeFeriasNull() ? "" : client.CidadeFerias),
                                     new SqlParameter("MoradaFerias", client.IsMoradaFeriasNull() ? "" : client.MoradaFerias),
                                     new SqlParameter("CodigoPostalFerias", client.IsCodigoPostalFeriasNull() ? "" : client.CodigoPostalFerias),
                                     new SqlParameter("ZonaFerias", client.IsZonaFeriasNull() ? "" : client.ZonaFerias),
                                     new SqlParameter("ObservacoesFerias", client.IsObservacoesFeriasNull() ? "" : client.ObservacoesFerias),
                                     new SqlParameter("CidadeEmprego", client.IsCidadeEmpregoNull() ? "" : client.CidadeEmprego),
                                     new SqlParameter("MoradaEmprego", client.IsMoradaEmpregoNull() ? "" : client.MoradaEmprego),
                                     new SqlParameter("CodigoPostalEmprego", client.IsCodigoPostalEmpregoNull() ? "": client.CodigoPostalEmprego),
                                     new SqlParameter("ZonaEmprego", client.IsZonaEmpregoNull() ? "" : client.ZonaEmprego),
                                     new SqlParameter("ObservacoesEmprego", client.IsObservacoesEmpregoNull() ? "" : client.ObservacoesEmprego),
                                     new SqlParameter("UserInitials", userInitials),
                                     new SqlParameter("Date", date),
                                     new SqlParameter("Time", time)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
            return number;
        }

        private static long UpdateClient(SqlTransaction sqlTransaction, ClienteDataSet.ClienteRow client)
        {
            var userInitials = UserInitials(sqlTransaction);
            var dateWithoutTime = GetDateWithoutTime(sqlTransaction);
            var time = GetTime(sqlTransaction);
            const string query =
                @"  update	cl
	                set		nome = @Nome,
			                local = @CidadeCasa,
			                u_Morada = @MoradaCasa,
			                morada = @MoradaCasa,
			                codpost = @CodigoPostalCasa,
			                zona = @ZonaCasa,
			                telefone = @Telefone,	
			                tlmvl = @Telemovel1,
			                c1tele = @Telemovel2,
			                c2tele = @Telemovel3,
			                c3tele = @Telemovel1,			
			                u_Observa = @ObservacoesCasa,
			                ncont = @Contribuinte,			
			                email = @Email,
                            nascimento = @DataNascimento,
                            u_LocalF = @CidadeFerias, 
                            u_MoradaF = @MoradaFerias,  
                            u_CodPostF = @CodigoPostalFerias, 
                            u_ZonaF = @ZonaFerias,  
                            u_ObservaF = @ObservacoesFerias,
                            u_LocalE = @CidadeEmprego,  
                            u_MoradaE = @MoradaEmprego,
                            u_CodPostE = @CodigoPostalEmprego, 
                            u_ZonaE = @ZonaEmprego,  
                            u_ObservaE = @ObservacoesEmprego,
			                usrinis = @UserInitials,
			                usrdata = @DateWithoutTime,				
			                usrhora = @Time
	                where	no = @Numero";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("Nome", client.Nome),
                                     new SqlParameter("Numero", client.Numero),
                                     new SqlParameter("Telefone", client.IsTelefoneNull() ? Convert.DBNull : client.Telefone),
                                     new SqlParameter("Telemovel1", client.IsTelemovel1Null() ? Convert.DBNull : client.Telemovel1),
                                     new SqlParameter("Telemovel2", client.IsTelemovel2Null() ? Convert.DBNull : client.Telemovel2),
                                     new SqlParameter("Telemovel3", client.IsTelemovel3Null() ? Convert.DBNull : client.Telemovel3),
                                     new SqlParameter("Contribuinte", client.IsContribuinteNull() ? Convert.DBNull : client.Contribuinte),
                                     new SqlParameter("Email", client.IsEmailNull() ? Convert.DBNull : client.Email),
                                     new SqlParameter("DataNascimento", client.IsDataNascimentoNull() || client.DataNascimento == DateTime.MinValue ? new DateTime(1900, 3, 1) : client.DataNascimento),
                                     new SqlParameter("CidadeCasa", client.IsCidadeCasaNull() ? "" : client.CidadeCasa),
                                     new SqlParameter("MoradaCasa", client.IsMoradaCasaNull() ? "" : client.MoradaCasa),
                                     new SqlParameter("CodigoPostalCasa", client.IsCodigoPostalCasaNull() ? "" : client.CodigoPostalCasa),
                                     new SqlParameter("ZonaCasa", client.IsZonaCasaNull() ? "" : client.ZonaCasa),
                                     new SqlParameter("ObservacoesCasa", client.IsObservacoesCasaNull() ? "" : client.ObservacoesCasa),                                     
                                     new SqlParameter("CidadeFerias", client.IsCidadeFeriasNull() ? "" : client.CidadeFerias),
                                     new SqlParameter("MoradaFerias", client.IsMoradaFeriasNull() ? "" : client.MoradaFerias),
                                     new SqlParameter("CodigoPostalFerias", client.IsCodigoPostalFeriasNull() ? "" : client.CodigoPostalFerias),
                                     new SqlParameter("ZonaFerias", client.IsZonaFeriasNull() ? "" : client.ZonaFerias),
                                     new SqlParameter("ObservacoesFerias", client.IsObservacoesFeriasNull() ? "" : client.ObservacoesFerias),
                                     new SqlParameter("CidadeEmprego", client.IsCidadeEmpregoNull() ? "" : client.CidadeEmprego),
                                     new SqlParameter("MoradaEmprego", client.IsMoradaEmpregoNull() ? "" : client.MoradaEmprego),
                                     new SqlParameter("CodigoPostalEmprego", client.IsCodigoPostalEmpregoNull() ? "": client.CodigoPostalEmprego),
                                     new SqlParameter("ZonaEmprego", client.IsZonaEmpregoNull() ? "" : client.ZonaEmprego),
                                     new SqlParameter("ObservacoesEmprego", client.IsObservacoesEmpregoNull() ? "" : client.ObservacoesEmprego),
                                     new SqlParameter("UserInitials", userInitials),
                                     new SqlParameter("DateWithoutTime", dateWithoutTime),
                                     new SqlParameter("Time", time)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
            return client.Numero;
        }

        private static void InsertSale2(SqlTransaction sqlTransaction, PedidoDataSet.PedidoRow request)
        {
            const string query =
                @"  insert 
                    into    ft2 (ft2stamp, morada, local, codpost, telefone, email,
                            evdinheiro,
                            vdinheiro, codfornecedor)
                    values(
                            @ExternoId, @Morada, @Cidade,  @CodigoPostal, @Telefone, @EMail,
                            case when @TipoPagamento like 'DINHEIRO' then @PaidValue else 0 end,
                            case when @TipoPagamento like 'DINHEIRO' then @PaidValue*200.482 else 0 end, 0)";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("ExternoId", request.ExternoId),
                                     new SqlParameter("Morada", request.IsMoradaNull() ? Convert.DBNull : request.Morada),
                                     new SqlParameter("Cidade", request.IsCidadeNull() ? Convert.DBNull : request.Cidade),
                                     new SqlParameter("CodigoPostal", request.IsCodigoPostalNull() ? Convert.DBNull : request.CodigoPostal),
                                     new SqlParameter("Telefone", request.IsTelefoneNull() ? Convert.DBNull : request.Telefone),
                                     new SqlParameter("EMail", request.IsEMailNull() ? Convert.DBNull : request.EMail),
                                     new SqlParameter("TipoPagamento", request.TipoPagamento),
                                     new SqlParameter("PaidValue", request.ValorPago)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
        }

        private static decimal GetVatPercentage(SqlTransaction sqlTransaction, int number)
        {
            const string query =
                @"  select  cast(replace(valor,',','.') as numeric(5,2))
                    from	para1 
                    where	descricao = @Active";
            var active = "ativa" + number;
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<decimal>(sqlTransaction, 0, query, new SqlParameter("Active", active));
        }

        private static void GetSaleDocumentTypeData(SqlTransaction sqlTransaction, string shop, out long documentType, out long documentTypeNumber, out string documentTypeName)
        {
            var table = new DataTable();
            const string query =
                @"  select  postd.ndoc, postd.nmdoc, td.tipodoc 
                    from    postd, pos, td 
                    where   pos.site = @Shop 
                            and postd.posstamp = pos.posstamp 
                            and td.ndoc = postd.ndoc";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("Shop", shop));
            var row = table.Rows[0];
            documentTypeNumber = Convert.ToInt64(row["ndoc"]);
            documentTypeName = row["nmdoc"].ToString();
            documentType = Convert.ToInt64(row["tipodoc"]);
        }

        private static void GetPosData(SqlTransaction sqlTransaction, string shop, out string posName, out long posNumber)
        {
            var table = new DataTable();
            const string query =
                @"  select	pnome, pno
	                from	pos
                    where   site = @Loja";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("Loja", shop));
            var row = table.Rows[0];
            posName = row["pnome"].ToString();
            posNumber = Convert.ToInt64(row["pno"]);
        }

        private static void GetSessionData(SqlTransaction sqlTransaction, string sessionStamp, out string cashStamp, out string cashUserName, out string sessionUserName)
        {
            var table = new DataTable();
            const string query =
                @"  select	cxstamp, cxusername, ausername
                    from	ss 
                    where	ssstamp = @SessionStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("SessionStamp", sessionStamp));
            var row = table.Rows[0];
            cashStamp = row["cxstamp"].ToString();
            cashUserName = row["cxusername"].ToString();
            sessionUserName = row["ausername"].ToString();
        }

        private static long GetSaleNumber(SqlTransaction sqlTransaction, int year, long documentTypeNumber)
        {
            const string query =
                @"  select  case when max(fno) + 1 > 0 then max(fno) + 1 else 1 end
                    from    ft
                    where	ftano = @Year
                            and ndoc = @DocumentTypeNumber";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<long>(sqlTransaction, 0, query, new SqlParameter("Year", year), new SqlParameter("DocumentTypeNumber", documentTypeNumber));
        }

        private static void InsertSale(SqlTransaction sqlTransaction, string sessionStamp, PedidoDataSet.PedidoRow request)
        {
            request.ExternoId = GenerateStamp(sqlTransaction);
            InsertSale2(sqlTransaction, request);
            var date = GetDateWithoutTime(sqlTransaction);
            var time = GetTime(sqlTransaction);
            var userInitials = UserInitials(sqlTransaction);
            var arriveDate = DateTime.Now.AddHours(1);
            var arriveTime = arriveDate.Hour.ToString().PadLeft(2, '0') + arriveDate.Minute.ToString().PadLeft(2, '0');
            var vatPercentage1 = GetVatPercentage(sqlTransaction, 1);
            var vatPercentage2 = GetVatPercentage(sqlTransaction, 2);
            var vatPercentage3 = GetVatPercentage(sqlTransaction, 3);
            var isCheck = request.TipoPagamento == PaymentType.CHEQUE.ToString() ? 1 : 0;
            string posName, cashStamp, cashUserName, sessionUserName, documentTypeName;
            long posNumber, documentType, documentTypeNumber;
            GetSaleDocumentTypeData(sqlTransaction, request.Loja, out documentType, out documentTypeNumber, out documentTypeName);
            GetPosData(sqlTransaction, request.Loja, out posName, out posNumber);
            GetSessionData(sqlTransaction, sessionStamp, out cashStamp, out cashUserName, out sessionUserName);
            long saleNumber = GetSaleNumber(sqlTransaction, date.Year, documentTypeNumber);
            const string query =
                @"  insert 
                    into    ft (
                            ftstamp, pais, ftano, fno, nmdoc, ndoc, no, 
                            nome, morada, local, 
                            codpost, ncont,	telefone, zona, fdata, 
                            pdata, cData, chora, tpdesc, chData, tipodoc,
                            ousrinis, site, pnome, pno, cxstamp, cxusername, ssstamp, ssusername, 
                            ousrdata, ousrhora, usrinis, usrdata, usrhora, saida, cambio, ivatx1, ivatx2,
                            ivatx3, moeda, cheque, memissao, encmdesc) 
                    values	(
                            @SaleStamp, 1, @Year, @SaleNumber, @DocumentTypeName, @DocumentTypeNumber, @ClientNumber, 
                            substring(@ClientName, 1, 55), substring(@ClientStreet, 1, 55), substring(@ClientCity, 1, 43), 
                            substring(@ClientZipCode, 1, 45), @ClientFiscalNumber, @ClientPhone, substring(@ClientZone, 1, 13), @Date, 
                            @Date, @ArriveDate, @ArriveTime, @PaymentType, @Date, @DocumentType,
                            @UserInitials, @PosSite, @PosName, @PosNumber, @CashStamp, @CashUserName, @SessionStamp, @SessionUserName,		
                            @Date, @Time, @UserInitials, @Date, @Time, substring(@Time, 1, 5), 1, @VatPercentage1, @VatPercentage2,		
                            @VatPercentage3, 'PTE ou EURO', @IsCheck, 'EURO', substring(@ClientRemarks, 1, 70))";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("SaleStamp", request.ExternoId),
                                     new SqlParameter("Year", date.Year),
                                     new SqlParameter("SaleNumber", saleNumber),
                                     new SqlParameter("DocumentTypeName", documentTypeName),
                                     new SqlParameter("DocumentTypeNumber", documentTypeNumber),
                                     new SqlParameter("ClientNumber", request.Cliente),
                                     new SqlParameter("ClientName", request.ClienteNome),
                                     new SqlParameter("ClientStreet", request.IsMoradaNull() ? Convert.DBNull : request.Morada),
                                     new SqlParameter("ClientCity", request.IsCidadeNull() ? Convert.DBNull : request.Cidade),
                                     new SqlParameter("ClientZipCode", request.IsCodigoPostalNull() ? Convert.DBNull : request.CodigoPostal),
                                     new SqlParameter("ClientFiscalNumber", request.IsContribuinteNull() ? Convert.DBNull : request.Contribuinte),
                                     new SqlParameter("ClientPhone", request.IsTelefoneNull() ? Convert.DBNull : request.Telefone),
                                     new SqlParameter("ClientZone", request.Zona),
                                     new SqlParameter("Date", date.Date),
                                     new SqlParameter("ArriveDate", arriveDate.Date),
                                     new SqlParameter("ArriveTime", arriveTime),
                                     new SqlParameter("PaymentType", request.TipoPagamento),
                                     new SqlParameter("DocumentType", documentType),
                                     new SqlParameter("UserInitials", userInitials),
                                     new SqlParameter("PosSite", request.Loja),
                                     new SqlParameter("PosName", posName),
                                     new SqlParameter("PosNumber", posNumber),
                                     new SqlParameter("CashStamp", cashStamp),
                                     new SqlParameter("CashUserName", cashUserName),
                                     new SqlParameter("SessionStamp", sessionStamp),
                                     new SqlParameter("SessionUserName", sessionUserName),
                                     new SqlParameter("Time", time),
                                     new SqlParameter("VatPercentage1", vatPercentage1),
                                     new SqlParameter("VatPercentage2", vatPercentage2),
                                     new SqlParameter("VatPercentage3", vatPercentage3),
                                     new SqlParameter("IsCheck", isCheck),
                                     new SqlParameter("ClientRemarks", request.IsPontoReferenciaNull() ? Convert.DBNull : request.PontoReferencia)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
            request.ExternoNumero = saleNumber.ToString();
            request.ExternoUtilizador = UserName(sqlTransaction);
        }

        private static void UpdateSale2(SqlTransaction sqlTransaction, PedidoDataSet.PedidoRow request)
        {
            const string query =
                @"  update	ft2 
                    set		morada = substring(@ClientStreet, 1, 55),					
                            local = substring(@ClientCity, 1, 43),
                            codpost = substring(@ClientZipCode, 1, 45),			
                            telefone = @ClientPhone,			
                            email = substring(@ClientMail, 1, 45),
                            evdinheiro = case when @PaymentType like 'DINHEIRO' then @PaidValue else 0 end,
                            vdinheiro = case when @PaymentType like 'DINHEIRO' then @PaidValue*200.482 else 0 end
                    where	ft2stamp = @SaleStamp";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("SaleStamp", request.ExternoId),
                                     new SqlParameter("ClientStreet", request.IsMoradaNull() ? Convert.DBNull : request.Morada),
                                     new SqlParameter("ClientCity", request.IsCidadeNull() ? Convert.DBNull : request.Cidade),
                                     new SqlParameter("ClientZipCode", request.IsCodigoPostalNull() ? Convert.DBNull : request.CodigoPostal),
                                     new SqlParameter("ClientPhone", request.IsTelefoneNull() ? Convert.DBNull : request.Telefone),
                                     new SqlParameter("ClientMail", request.IsEMailNull() ? Convert.DBNull : request.EMail),
                                     new SqlParameter("PaymentType", request.TipoPagamento),
                                     new SqlParameter("PaidValue", request.ValorPago)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
        }

        private static void UpdateSale(SqlTransaction sqlTransaction, PedidoDataSet.PedidoRow request)
        {
            UpdateSale2(sqlTransaction, request);
            var date = GetDateWithoutTime(sqlTransaction);
            var time = GetTime(sqlTransaction);
            var userInitials = UserInitials(sqlTransaction);
            var arriveDate = DateTime.Now.AddHours(1);
            var arriveTime = arriveDate.Hour.ToString().PadLeft(2, '0') + arriveDate.Minute.ToString().PadLeft(2, '0');
            var isCheck = request.TipoPagamento == PaymentType.CHEQUE.ToString() ? 1 : 0;
            const string query =
                @"  update	ft 
                    set		nome = substring(@ClientName, 1, 55),
                            morada = substring(@ClientStreet, 1, 55),
                            local = substring(@ClientCity, 1, 43),
                            codpost = substring(@ClientZipCode, 1, 45),			
                            ncont = @ClientFiscalNumber,
                            telefone = @ClientPhone,				
                            zona = substring(@ClientZone, 1, 13),
                            cData = @ArriveDate,
                            chora = @ArriveTime,
                            tpdesc = @PaymentType,				
                            usrinis = @UserInitials,
                            usrdata = @Date,				
                            usrhora = @Time,
                            cheque = @IsCheck
                    where	ftstamp = @SaleStamp";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("SaleStamp", request.ExternoId),
                                     new SqlParameter("ClientName", request.ClienteNome),
                                     new SqlParameter("ClientStreet", request.IsMoradaNull() ? Convert.DBNull : request.Morada),
                                     new SqlParameter("ClientCity", request.IsCidadeNull() ? Convert.DBNull : request.Cidade),
                                     new SqlParameter("ClientZipCode", request.IsCodigoPostalNull() ? Convert.DBNull : request.CodigoPostal),
                                     new SqlParameter("ClientFiscalNumber", request.IsContribuinteNull() ? Convert.DBNull : request.Contribuinte),
                                     new SqlParameter("ClientPhone", request.IsTelefoneNull() ? Convert.DBNull : request.Telefone),
                                     new SqlParameter("ClientZone", request.Zona),
                                     new SqlParameter("ArriveDate", arriveDate.Date),
                                     new SqlParameter("ArriveTime", arriveTime),
                                     new SqlParameter("PaymentType", request.TipoPagamento),
                                     new SqlParameter("UserInitials", userInitials),
                                     new SqlParameter("Date", date),
                                     new SqlParameter("Time", time),
                                     new SqlParameter("IsCheck", isCheck)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
            request.ExternoUtilizador = UserName(sqlTransaction);
        }

        private static void DeleteSaleItems(SqlTransaction sqlTransaction, string saleStamp)
        {
            const string query =
                @"  delete	fi
	                where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp));
        }

        private static void GetClientData(SqlTransaction sqlTransaction, long clientNumber, out int clientPrice)
        {
            var table = new DataTable();
            const string query =
                @"  select	case preco when 0 then 1 else preco end as price
                    from	cl
                    where	no = @ClientNumber";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("ClientNumber", clientNumber));
            var row = table.Rows[0];
            clientPrice = Convert.ToInt32(row["price"]);
        }

        private static void GetProductData(SqlTransaction sqlTransaction, string reference, out string productGroupName, out int withVat, out long vatTable, out decimal productCost)
        {
            var table = new DataTable();
            const string query =
                @"  select  usr4, ivaincl, tabiva, epcusto			
                    from	st 
                    where	ref = @Reference";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("Reference", reference));
            var row = table.Rows[0];
            productGroupName = row["usr4"].ToString();
            withVat = Convert.ToInt32(row["ivaincl"]);
            vatTable = Convert.ToInt64(row["tabiva"]);
            productCost = Convert.ToDecimal(row["epcusto"]);
        }

        private static int GetOrder(SqlTransaction sqlTransaction, string saleStamp)
        {
            const string query =
                @"  select	count(*) + 1
                    from	fi 
                    where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("SaleStamp", saleStamp));
        }

        private static int GetWarehouse(SqlTransaction sqlTransaction, string shop)
        {
            const string query =
                @"  select	armazem
                    from	pos
                    where   site = @Shop";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Shop", shop));
        }

        private static void InsertSaleItem(SqlTransaction sqlTransaction, PedidoDataSet.PedidoRow request, PedidoDataSet.ItemPedidoRow requestItem)
        {
            string documentTypeName, productGroupName;
            long documentType, documentTypeNumber, vatTable;
            int clientPrice, withVat;
            decimal productCost;
            GetSaleDocumentTypeData(sqlTransaction, request.Loja, out documentType, out documentTypeNumber, out documentTypeName);
            GetClientData(sqlTransaction, request.Cliente, out clientPrice);
            GetProductData(sqlTransaction, requestItem.ReferenciaProduto, out productGroupName, out withVat, out vatTable, out productCost);
            var order = GetOrder(sqlTransaction, request.ExternoId);
            var warehouse = GetWarehouse(sqlTransaction, request.Loja);
            var saleItemStamp = GenerateStamp(sqlTransaction) + "," + order.ToString().PadLeft(3, '0');
            var date = GetDateWithoutTime(sqlTransaction);
            var time = GetTime(sqlTransaction);
            var userInitials = UserInitials(sqlTransaction);
            var liquidValue = requestItem.Valor * requestItem.Quantidade;
            var liquidWithoutVatValue = liquidValue / (1 + (requestItem.PercentagemIva / 100));
            var unitLiquidWithoutVatValue = requestItem.Valor / (1 + (requestItem.PercentagemIva / 100));
            const string query =
                @"  insert 
                    into    fi(
                            fistamp, nmdoc, fno, ref, design, qtt, iva, ivaincl, 
                            tabiva, ndoc, armazem, rdata, lordem, usr4, ftstamp, stipo, tipodoc,			
                            familia, stns, ecusto, preco, sujirs, epcp, ousrinis, ousrdata, ousrhora, usrinis,				
                            usrdata, usrhora, zona, morada, local, 
                            codpost, telefone, email, etiliquido,
                            tiliquido, tliquido, epv, pv,	
                            eslvu, slvu,				
                            esltt, sltt)
                    values (
                            @SaleItemStamp, @DocumentTypeName, @SaleNumber,	@Reference, @ProductName, @Quantity, @VatPercentage, @WithVat,				
                            @VatTable, @DocumentTypeNumber, @Warehouse, @Date, @Order, @ProductGroupName, @SaleStamp, 1, @DocumentType,		
                            @Family, @IsService, @ProductCost, @ClientPrice, 1, @ProductCost, @UserInitials, @Date, @Time, @UserInitials,	
                            @Date, @Time, substring(@ClientZone, 1, 13), substring(@ClientStreet, 1, 55), substring(@ClientCity, 1, 43), 
                            substring(@ClientZipCode, 1, 45), @ClientPhone, substring(@ClientEmail, 1, 45), @LiquidValue,		
                            round(@LiquidValue*200.482,0), @LiquidValue, @Price, round(@Price*200.482, 1),
                            round(@UnitLiquidWithoutVatValue, 2), round(round(@UnitLiquidWithoutVatValue, 2)*200.482, 1),	
                            @LiquidWithoutVatValue,	@LiquidWithoutVatValue*200.482)";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("SaleItemStamp", saleItemStamp),
                                     new SqlParameter("DocumentTypeName", documentTypeName),
                                     new SqlParameter("SaleNumber", request.ExternoNumero),
                                     new SqlParameter("Reference", requestItem.ReferenciaProduto),
                                     new SqlParameter("ProductName", requestItem.NomeProduto),
                                     new SqlParameter("Quantity", requestItem.Quantidade),
                                     new SqlParameter("VatPercentage", requestItem.PercentagemIva),
                                     new SqlParameter("WithVat", withVat),
                                     new SqlParameter("VatTable", vatTable),
                                     new SqlParameter("DocumentTypeNumber", documentTypeNumber),
                                     new SqlParameter("Warehouse", warehouse),
                                     new SqlParameter("Date", date.Date),
                                     new SqlParameter("Order", order),
                                     new SqlParameter("ProductGroupName", productGroupName),
                                     new SqlParameter("SaleStamp", request.ExternoId),
                                     new SqlParameter("DocumentType", documentType),
                                     new SqlParameter("Family", requestItem.Familia),
                                     new SqlParameter("IsService", requestItem.Stock),
                                     new SqlParameter("ProductCost", productCost),
                                     new SqlParameter("ClientPrice", clientPrice),
                                     new SqlParameter("UserInitials", userInitials),
                                     new SqlParameter("Time", time),
                                     new SqlParameter("ClientZone", request.Zona),
                                     new SqlParameter("ClientCity", request.Cidade),
                                     new SqlParameter("ClientStreet", request.IsMoradaNull() ? Convert.DBNull : request.Morada),
                                     new SqlParameter("ClientZipCode", request.IsCodigoPostalNull() ? Convert.DBNull : request.CodigoPostal),
                                     new SqlParameter("ClientPhone", request.IsTelefoneNull() ? Convert.DBNull : request.Telefone),
                                     new SqlParameter("ClientEmail", request.IsEMailNull() ? Convert.DBNull : request.EMail),
                                     new SqlParameter("LiquidValue", liquidValue),
                                     new SqlParameter("Price", requestItem.Valor),
                                     new SqlParameter("UnitLiquidWithoutVatValue", unitLiquidWithoutVatValue),
                                     new SqlParameter("LiquidWithoutVatValue", liquidWithoutVatValue)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());
        }

        private static void GetSaleItemTotals(SqlTransaction sqlTransaction, string saleStamp, out decimal quantity, out decimal totalWithVat, out decimal totalWithoutVat)
        {
            var table = new DataTable();
            const string query =
                @"  select  case when sum(qtt) > 0 then sum(qtt) else 0 end as quantity,
                            case when sum(etiliquido) > 0 then sum(etiliquido) else 0 end total_with_vat, 
                            case when sum(esltt) > 0 then sum(esltt) else 0 end as total_without_vat
                    from	fi
                    where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("SaleStamp", saleStamp));
            var row = table.Rows[0];
            quantity = Convert.ToDecimal(row["quantity"]);
            totalWithVat = Convert.ToDecimal(row["total_with_vat"]);
            totalWithoutVat = Convert.ToDecimal(row["total_without_vat"]);
        }

        private static void GetSaleItemTotalValue(SqlTransaction sqlTransaction, string saleStamp, int number, out decimal totalWithoutVat, out decimal totalVatValue)
        {
            var table = new DataTable();
            const string query =
                @"  select	case when sum(esltt) > 0 then sum(esltt) else 0 end as total_without_vat,
                            case when sum(etiliquido - esltt) > 0 then sum(etiliquido - esltt) else 0 end as total_vat
                    from	para1, fi
                    where	descricao = @Active
                            and fi.iva = cast(replace(valor,',','.') as numeric(5,2))
                            and fi.ftstamp = @SaleStamp";
            var active = "ativa" + number;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, table, query, new SqlParameter("SaleStamp", saleStamp), new SqlParameter("Active", active));
            var row = table.Rows[0];
            totalWithoutVat = Convert.ToDecimal(row["total_without_vat"]);
            totalVatValue = Convert.ToDecimal(row["total_vat"]);
        }

        private static string GetPaymentType(SqlTransaction sqlTransaction, int number)
        {
            const string query =
                @"  select	cast(rtrim(valor) as varchar(15))
                    from	para1 
                    where	descricao = @Payment";
            var payment = "pos_pag" + number;
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("Payment", payment));
        }

        private static void UpdatePaymentType1Values(SqlTransaction sqlTransaction, string saleStamp, string paymentType, string paymentType1, decimal totalWithVat)
        {
            const string query =
                @"  update	ft2
                    set		modop1 = @PaymentType1,
                            epaga1 = case when @PaymentType1 like @PaymentType then @TotalWithVat else 0 end,
                            paga1 = case when @PaymentType1 like @PaymentType then @TotalWithVat*200.482 else 0 end
                    where	ft2stamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp),
                    new SqlParameter("PaymentType1", paymentType1), new SqlParameter("PaymentType", paymentType),
                    new SqlParameter("TotalWithVat", totalWithVat));
        }

        private static void UpdatePaymentType2Values(SqlTransaction sqlTransaction, string saleStamp, string paymentType, string paymentType2, decimal totalWithVat)
        {
            const string query =
                @"  update	ft2
                    set		modop2 = @PaymentType2,
                            epaga2 = case when @PaymentType2 like @PaymentType then @TotalWithVat else 0 end,
                            paga2 = case when @PaymentType2 like @PaymentType then @TotalWithVat*200.482 else 0 end
                    where	ft2stamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp),
                    new SqlParameter("PaymentType2", paymentType2), new SqlParameter("PaymentType", paymentType),
                    new SqlParameter("TotalWithVat", totalWithVat));
        }

        private static void UpdatePaymentType3Values(SqlTransaction sqlTransaction, string saleStamp, string paymentType, string paymentType3, decimal totalWithVat)
        {
            const string query =
                @"  update	ft2
                    set		modop3 = @PaymentType3,
                            epaga3 = case when @PaymentType3 like @PaymentType then @TotalWithVat else 0 end,
                            paga3 = case when @PaymentType3 like @PaymentType then @TotalWithVat*200.482 else 0 end
                    where	ft2stamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp),
                    new SqlParameter("PaymentType3", paymentType3), new SqlParameter("PaymentType", paymentType),
                    new SqlParameter("TotalWithVat", totalWithVat));
        }

        private static void UpdateMoneyPaymentTypeValues(SqlTransaction sqlTransaction, string saleStamp, string paymentType, decimal totalWithVat)
        {
            const string query =
                @"  update	ft2
                    set		etroco = case when @PaymentType = 'DINHEIRO' then evdinheiro - @TotalWithVat else 0 end,
                            troco = case when @PaymentType = 'DINHEIRO' then (evdinheiro - @TotalWithVat)*200.482 else 0 end			
                    where	ft2stamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp),
                    new SqlParameter("PaymentType", paymentType), new SqlParameter("TotalWithVat", totalWithVat));
        }

        private static void UpdateSaleTotals(SqlTransaction sqlTransaction, string saleStamp, string paymentType)
        {
            decimal quantity, totalWithVat, totalWithoutVat, total1WithoutVat, total1Vat, total2WithoutVat, total2Vat, total3WithoutVat,
                total3Vat;
            GetSaleItemTotals(sqlTransaction, saleStamp, out quantity, out totalWithVat, out totalWithoutVat);
            GetSaleItemTotalValue(sqlTransaction, saleStamp, 1, out total1WithoutVat, out total1Vat);
            GetSaleItemTotalValue(sqlTransaction, saleStamp, 2, out total2WithoutVat, out total2Vat);
            GetSaleItemTotalValue(sqlTransaction, saleStamp, 3, out total3WithoutVat, out total3Vat);
            var totalWithoutVatValue = total1WithoutVat + total2WithoutVat + total3WithoutVat;
            var totalVatValue = total1Vat + total2Vat + total3Vat;

            const string query =
                @"  update	ft
                    set		totqtt = @Quantity,
                            qtt1 = @Quantity,
                            chtotal = case cheque when 1 then @TotalWithVat*200.482 else 0 end,
                            echtotal = case cheque when 1 then @TotalWithVat else 0 end,
                            chmoeda = case cheque when 1 then moeda else '' end,
                            etotal = @TotalWithVat,			    total = @TotalWithVat*200.482,
                            etot1 = @TotalValueWithoutVat,		tot1 = @TotalValueWithoutVat*200.482,
                            eivav1 = round(@Total1VatValue, 2),			ivav1 = round(@Total1VatValue, 2)*200.482,
                            eivain1 = round(@Total1ValueWithoutVat, 2),	ivain1 = round(@Total1ValueWithoutVat, 2)*200.482,
                            eivav2 = round(@Total2VatValue, 2),			ivav2 = round(@Total2VatValue, 2)*200.482,
                            eivain2 = round(@Total2ValueWithoutVat, 2),	ivain2 = round(@Total2ValueWithoutVat, 2)*200.482,
                            eivav3 = round(@Total3VatValue, 2),			ivav3 = round(@Total3VatValue, 2)*200.482,
                            eivain3 = round(@Total3ValueWithoutVat, 2),	ivain3 = round(@Total3ValueWithoutVat, 2)*200.482,
                            ettiva = round(@TotalVatValue, 2),			ttiva = round(@TotalVatValue, 2)*200.482,
                            ettiliq = round(@TotalValueWithoutVat, 2),	ttiliq = round(@TotalValueWithoutVat, 2)*200.482
                    where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp),
                    new SqlParameter("Quantity", quantity), new SqlParameter("TotalWithVat", totalWithVat),
                    new SqlParameter("TotalValueWithoutVat", totalWithoutVatValue), new SqlParameter("Total1VatValue", total1Vat),
                    new SqlParameter("Total1ValueWithoutVat", total1WithoutVat), new SqlParameter("Total2VatValue", total2Vat),
                    new SqlParameter("Total2ValueWithoutVat", total2WithoutVat), new SqlParameter("Total3VatValue", total3Vat),
                    new SqlParameter("Total3ValueWithoutVat", total3WithoutVat), new SqlParameter("TotalVatValue", totalVatValue));

            var paymentType1 = GetPaymentType(sqlTransaction, 1);
            UpdatePaymentType1Values(sqlTransaction, saleStamp, paymentType, paymentType1, totalWithVat);
            var paymentType2 = GetPaymentType(sqlTransaction, 2);
            UpdatePaymentType2Values(sqlTransaction, saleStamp, paymentType, paymentType2, totalWithVat);
            var paymentType3 = GetPaymentType(sqlTransaction, 3);
            UpdatePaymentType3Values(sqlTransaction, saleStamp, paymentType, paymentType3, totalWithVat);
            UpdateMoneyPaymentTypeValues(sqlTransaction, saleStamp, paymentType, totalWithVat);
        }

        private static void ClearEmployee(SqlTransaction sqlTransaction, string saleStamp)
        {
            const string saleQuery =
                @"  update	ft
		            set		vendnm = '',
			                vendedor = 0
                    where	ftstamp = @SaleStamp";
            const string saleItemQuery =
                @"  update	fi
		            set		fivendnm = '',
			                fivendedor = 0
                    where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.ExecuteNonQuery(sqlTransaction, saleQuery, new SqlParameter("SaleStamp", saleStamp));
                dataAccess.ExecuteNonQuery(sqlTransaction, saleItemQuery, new SqlParameter("SaleStamp", saleStamp));
            }
        }

        private static void SetEmployee(SqlTransaction sqlTransaction, string saleStamp, string employeeName)
        {
            const string saleQuery =
                @"  update	ft
		            set		vendnm = @EmployeeName,
			                vendedor = (select	cm from	cm3 where cmdesc = @EmployeeName)
                    where	ftstamp = @SaleStamp";
            const string saleItemQuery =
                @"  update	fi
		            set		fivendnm = @EmployeeName,
			                fivendedor = (select	cm from	cm3 where cmdesc = @EmployeeName)
			        where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.ExecuteNonQuery(sqlTransaction, saleQuery, new SqlParameter("SaleStamp", saleStamp), new SqlParameter("EmployeeName", employeeName));
                dataAccess.ExecuteNonQuery(sqlTransaction, saleItemQuery, new SqlParameter("SaleStamp", saleStamp), new SqlParameter("EmployeeName", employeeName));
            }
        }

        private static double GetPaidValue(SqlTransaction sqlTransaction, DateTime day, string shop)
        {
            const string query =
                @"  select	sum(fi.etiliquido)
	                from	ft, fi
	                where	ft.chData = @Day
                            and (rtrim(ltrim(ft.site)) = @Shop or @Shop = '')
                            and fi.ftstamp = ft.ftstamp
                            and ft.tpdesc = 'DINHEIRO'";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<double>(sqlTransaction, 0, query, new SqlParameter("Shop", shop.Trim()), new SqlParameter("Day", day.Date));
        }

        private static double GetMultiBankValue(SqlTransaction sqlTransaction, DateTime day, string shop)
        {
            const string query =
                @"  select	sum(fi.etiliquido)
	                from	ft, fi
	                where	ft.chData = @Day
                            and (rtrim(ltrim(ft.site)) = @Shop or @Shop = '')
                            and fi.ftstamp = ft.ftstamp
                            and ft.tpdesc = 'MULTIBANCO'";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<double>(sqlTransaction, 0, query, new SqlParameter("Shop", shop.Trim()), new SqlParameter("Day", day.Date));
        }

        private static double GetChangeValue(SqlTransaction sqlTransaction, DateTime day, string shop)
        {
            const string query =
                @"  select	sum(ft2.etroco)
	                from	ft, ft2 
	                where	ft.chData = @Day
                            and (rtrim(ltrim(ft.site)) = @Shop or @Shop = '')
                            and ft2.ft2stamp = ft.ftstamp";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<double>(sqlTransaction, 0, query, new SqlParameter("Shop", shop.Trim()), new SqlParameter("Day", day.Date));
        }

        private static double GetCheckValue(SqlTransaction sqlTransaction, DateTime day, string shop)
        {
            const string query =
                @"  select	sum(ft.echtotal)
	                from	ft
	                where	ft.chData = @Day
                            and (rtrim(ltrim(ft.site)) = @Shop  or @Shop = '')";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<double>(sqlTransaction, 0, query, new SqlParameter("Shop", shop.Trim()), new SqlParameter("Day", day.Date));
        }

        private static int GetClients(SqlTransaction sqlTransaction, DateTime day, string shop)
        {
            const string query =
                @"  select	count(distinct ft.no)
	                from	ft
	                where	ft.chData = @Day
                            and (rtrim(ltrim(ft.site)) = @Shop  or @Shop = '')";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query, new SqlParameter("Shop", shop.Trim()), new SqlParameter("Day", day.Date));
        }

        private static void GetMovments(SqlTransaction sqlTransaction, DateTime day, string shop, ListagemZDataSet.FamiliaDataTable dataTable)
        {
            const string query =
                @"  select	fi.familia, 
		                    sum(fi.qtt) as quantidade,
		                    fi.iva,
		                    sum(fi.esltt) as ValorSemIva,
		                    sum(fi.tliquido - fi.esltt) as ValorIva,
		                    sum(fi.tliquido) as ValorComIva,
                            rtrim(ltrim(ft.site)) as LocalVenda
                    from	ft, fi
                    where	ft.chData = @Day
                            and (rtrim(ltrim(ft.site)) = @Shop or @Shop = '')
                            and fi.ftstamp = ft.ftstamp		
                    group by familia, iva, rtrim(ltrim(ft.site))";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataTable, query, new SqlParameter("Shop", shop.Trim()), new SqlParameter("Day", day.Date));
        }



        public static string CashOpened(SqlTransaction sqlTransaction)
        {
            const string query =
                @"  select	cxstamp
	                from	cx 
	                where	fechada = 0";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query);
        }

        public static string OpenSession(SqlTransaction sqlTransaction, string cashStamp)
        {
            var sessionStamp = SessionStamp(sqlTransaction, cashStamp);
            if (sessionStamp != "")
                return sessionStamp;
            sessionStamp = GenerateStamp(sqlTransaction);
            var date = GetDateWithoutTime(sqlTransaction);
            var time = GetTime(sqlTransaction);
            string posSite, posName, cashUserName;
            int posNumber;
            var userName = UserName(sqlTransaction);
            var userNumber = UserNumber(sqlTransaction);
            var userInitials = UserInitials(sqlTransaction);
            GetCashData(sqlTransaction, cashStamp, out posSite, out posName, out posNumber, out cashUserName);
            InsertSession(sqlTransaction, sessionStamp, posSite, posName, posNumber, cashStamp, cashUserName, userName, userNumber, date, time, userInitials);
            return sessionStamp;
        }

        public static ZonaDataSet GetZones(SqlTransaction sqlTransaction, string shop)
        {
            var dataSet = new ZonaDataSet();
            const string query =
                @"  select	campo as nome,
                            u_ref as produto,
                            u_site as loja
	                from	dyTable
                    where   entityname = 'A_ZONAS'
                            and (rtrim(ltrim(u_site)) = @Loja or @Loja = '')";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Zona, query, new SqlParameter("Loja", shop.Trim()));
            return dataSet;
        }

        public static FuncionarioDataSet GetEmployees(SqlTransaction sqlTransaction, string shop)
        {
            var dataSet = new FuncionarioDataSet();
            const string query =
                @"  select	cmdesc as nome
	                from	cm3
                    where	inactivo = 0
                            and (rtrim(ltrim(u_site)) like @Shop or @Shop = '')
                    order by cmdesc";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Funcionario, query, new SqlParameter("Shop", shop.Trim()));
            return dataSet;
        }

        public static ClienteDataSet GetClients(SqlTransaction sqlTransaction, string name, string phone, string fiscalNumber, string street)
        {
            var dataSet = new ClienteDataSet();
            const string query =
                @"  select	nome as Nome, 
                            local as CidadeCasa, 
                            u_Morada as MoradaCasa, 
                            codpost as CodigoPostalCasa, 
                            zona as ZonaCasa, 
                            telefone as Telefone, 
                            tlmvl as Telemovel1, 
                            c1tele as Telemovel2, 
                            c2tele as Telemovel3, 
                            u_Observa as ObservacoesCasa, 
                            ncont as Contribuinte, 
                            email as Email, 
                            no as Numero, 
                            nascimento as DataNascimento,
                            (select u_site from dytable where entityname = 'A_ZONAS' and campo = cl.zona) as LojaCasa,
                            (select u_site from dytable where entityname = 'A_ZONAS' and campo = cl.u_ZonaF) as LojaFerias,
                            u_LocalF as CidadeFerias,
                            u_MoradaF as MoradaFerias, 
                            u_CodPostF as CodigoPostalFerias, 
                            u_ZonaF as ZonaFerias,
                            u_ObservaF as ObservacoesFerias,
                            (select u_site from dytable where entityname = 'A_ZONAS' and campo = cl.u_ZonaE) as LojaEmpresa,
                            u_LocalE as CidadeEmprego,
                            u_MoradaE as MoradaEmprego, 
                            u_CodPostE as CodigoPostalEmprego, 
                            u_ZonaE as ZonaEmprego,
                            u_ObservaE as ObservacoesEmprego
	                from	cl";
            string filter = "";
            var parameters = new List<SqlParameter>();
            if (name != "")
            {
                filter += "upper(nome) like '%'+@Name+'%'";
                parameters.Add(new SqlParameter("Name", name.ToUpper()));
            }
            if (phone != "")
            {
                filter += (filter != "") ? " and " : "";
                filter += "(telefone = @Phone or tlmvl = @Phone or c1tele = @Phone or c2tele = @Phone)";
                parameters.Add(new SqlParameter("Phone", phone));
            }
            if (fiscalNumber != "")
            {
                filter += (filter != "") ? " and " : "";
                filter += "ncont = @FiscalNumber";
                parameters.Add(new SqlParameter("FiscalNumber", fiscalNumber));
            }
            if (street != "")
            {
                filter += (filter != "") ? " and " : "";
                filter += "upper(u_Morada) like '%'+@Street+'%'";
                parameters.Add(new SqlParameter("Street", street.ToUpper()));
            }
            if(filter != "")
                filter = " where " + filter;
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Cliente, query + filter, parameters.ToArray());
            return dataSet;
        }

        public static ClienteDataSet GetClient(SqlTransaction sqlTransaction, long clientId)
        {
            var dataSet = new ClienteDataSet();
            const string query =
                @"  select	nome as Nome, 
                            local as CidadeCasa, 
                            u_Morada as MoradaCasa, 
                            codpost as CodigoPostalCasa, 
                            zona as ZonaCasa, 
                            telefone as Telefone, 
                            tlmvl as Telemovel1, 
                            c1tele as Telemovel2, 
                            c2tele as Telemovel3, 
                            u_Observa as ObservacoesCasa, 
                            ncont as Contribuinte, 
                            email as Email, 
                            no as Numero, 
                            nascimento as DataNascimento,
                            (select u_site from dytable where entityname = 'A_ZONAS' and campo = cl.zona) as LojaCasa,
                            (select u_site from dytable where entityname = 'A_ZONAS' and campo = cl.u_ZonaF) as LojaFerias,
                            u_LocalF as CidadeFerias,
                            u_MoradaF as MoradaFerias, 
                            u_CodPostF as CodigoPostalFerias, 
                            u_ZonaF as ZonaFerias,
                            u_ObservaF as ObservacoesFerias,
                            (select u_site from dytable where entityname = 'A_ZONAS' and campo = cl.u_ZonaE) as LojaEmpresa,
                            u_LocalE as CidadeEmprego,
                            u_MoradaE as MoradaEmprego, 
                            u_CodPostE as CodigoPostalEmprego, 
                            u_ZonaE as ZonaEmprego,
                            u_ObservaE as ObservacoesEmprego
	                from	cl	
                    where   no = @Number";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.Cliente, query, new SqlParameter("Number", clientId));
            return dataSet;
        }

        public static ProdutoDataSet GetProductsOfShop(SqlTransaction sqlTransaction, string shop)
        {
            var dataSet = new ProdutoDataSet();
            const string familyQuery =
                @"  select	case when txtqlook like '' then ref else txtqlook end as Familia
	                from	stfami
	                where	qlook = 1
                            and rtrim(ltrim(u_site)) = @Shop
                    order by 1";
            const string categoryQuery =
                @"  select	distinct case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end as Familia, 
                            st.usr4 as Categoria
	                from	st, stfami
	                where	st.qlook = 1 
                            and rtrim(ltrim(u_site)) = @Shop
			                and st.inactivo = 0 
			                and st.bloqueado = 0 
			                and stfami.qlook = 1 			                
			                and st.familia = stfami.ref
                    order by 1, 2";
            const string productQuery =
                @"  select	case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end as Familia, 
                            st.usr4 as Categoria,
                            st.ref as Referencia,
                            st.design as Descricao,
                            st.epv1 as Valor, 
                            stobs.stobs as Acompanhamento,
                            st.stns as Stock, 
			                ti.taxa as PercentagemIva, 
                            st.epcusto as PrecoTabela
                    from	st left outer join stobs on (stobs.ref = st.ref), stfami, taxasiva ti
	                where	st.qlook = 1 
                            and rtrim(ltrim(u_site)) = @Shop
			                and st.inactivo = 0 
			                and st.bloqueado = 0 
			                and stfami.qlook = 1 
			                and st.familia = stfami.ref 
			                and ti.codigo = st.tabiva
                    order by 1, 2, 4";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Familia, familyQuery, new SqlParameter("Shop", shop.Trim()));
                dataAccess.Fill(sqlTransaction, dataSet.Categoria, categoryQuery, new SqlParameter("Shop", shop.Trim()));
                dataAccess.Fill(sqlTransaction, dataSet.Produto, productQuery, new SqlParameter("Shop", shop.Trim()));
            }
            return dataSet;
        }

        public static ProdutoDataSet GetProduct(SqlTransaction sqlTransaction, string family, string category, string productReference)
        {
            var dataSet = new ProdutoDataSet();
            const string familyQuery =
                @"  select	case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end as Familia
	                from	stfami
	                where	qlook = 1
                            and rtrim(ltrim(case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end)) like @Family
                    order by 1";
            const string categoryQuery =
                @"  select	distinct case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end as Familia, 
                            st.usr4 as Categoria
	                from	st, stfami
	                where	st.qlook = 1 
                            and st.inactivo = 0 
			                and st.bloqueado = 0 
			                and stfami.qlook = 1 			                
			                and st.familia = stfami.ref
                            and rtrim(ltrim(case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end)) like @Family
                            and rtrim(ltrim(st.usr4)) like @Category
                    order by 1, 2";
            const string productQuery =
                @"  select	case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end as Familia, 
                            st.usr4 as Categoria,
                            st.ref as Referencia,
                            st.design as Descricao,
                            st.epv1 as Valor, 
                            stobs.stobs as Acompanhamento,
                            st.stns as Stock, 
			                ti.taxa as PercentagemIva,
                            st.epcusto as PrecoTabela
                    from	st left outer join stobs on (stobs.ref = st.ref), stfami, taxasiva ti
	                where	st.qlook = 1 
                            and st.inactivo = 0 
			                and st.bloqueado = 0 
			                and stfami.qlook = 1 
			                and st.familia = stfami.ref 
			                and ti.codigo = st.tabiva
                            and rtrim(ltrim(case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end)) like @Family
                            and rtrim(ltrim(st.usr4)) like @Category
                            and rtrim(ltrim(st.ref)) like @ProductReference
                    order by 1, 2, 4";
            using (var dataAccess = new SqlDataAccess())
            {
                dataAccess.Fill(sqlTransaction, dataSet.Familia, familyQuery, new SqlParameter("Family", family.Trim()));
                dataAccess.Fill(sqlTransaction, dataSet.Categoria, categoryQuery, new SqlParameter("Family", family.Trim()), new SqlParameter("Category", category.Trim()));
                dataAccess.Fill(sqlTransaction, dataSet.Produto, productQuery, new SqlParameter("Family", family.Trim()), new SqlParameter("Category", category.Trim()),
                    new SqlParameter("ProductReference", productReference.Trim()));
            }
            return dataSet;
        }

        public static string GetProductFamily(SqlTransaction sqlTransaction, string productReference)
        {
            const string query =
                @"  select	case when stfami.txtqlook like '' then stfami.ref else stfami.txtqlook end as Familia
                    from	st, stfami
	                where	st.qlook = 1 
                            and st.inactivo = 0 
			                and st.bloqueado = 0 
			                and stfami.qlook = 1 
			                and st.familia = stfami.ref 
			                and rtrim(ltrim(st.ref)) = @ProductReference";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("ProductReference", productReference.Trim()));
        }

        public static string GetProductCategory(SqlTransaction sqlTransaction, string productReference)
        {
            const string query =
                @"  select	usr4 as Categoria
                    from	st
	                where	qlook = 1 
                            and inactivo = 0 
			                and bloqueado = 0 
			                and rtrim(ltrim(ref)) = @ProductReference";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("ProductReference", productReference.Trim()));
        }

        public static decimal Stock(SqlTransaction sqlTransaction, string productReference)
        {
            const string query =
                @"  select	stock 
	                from    st 
	                where	rtrim(ltrim(ref)) = @Reference";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar<decimal>(sqlTransaction, 0, query, new SqlParameter("Reference", productReference.Trim()));          
        }

        public static bool SaveClient(SqlTransaction sqlTransaction, ClienteDataSet.ClienteRow client)
        {
            client.Numero = client.Numero > 0 ? UpdateClient(sqlTransaction, client) : InsertClient(sqlTransaction, client);
            return client.Numero > 0;
        }

        public static void SaveSale(SqlTransaction sqlTransaction, string sessionStamp, PedidoDataSet request)
        {
            if (request.Pedido[0].IsExternoIdNull())
                InsertSale(sqlTransaction, sessionStamp, request.Pedido[0]);
            else
                UpdateSale(sqlTransaction, request.Pedido[0]);
            DeleteSaleItems(sqlTransaction, request.Pedido[0].ExternoId);
            foreach (PedidoDataSet.ItemPedidoRow requestItem in request.ItemPedido.Select("", "", DataViewRowState.CurrentRows))
                InsertSaleItem(sqlTransaction, request.Pedido[0], requestItem);
            UpdateSaleTotals(sqlTransaction, request.Pedido[0].ExternoId, request.Pedido[0].TipoPagamento);
        }

        public static void AssignEmployee(SqlTransaction sqlTransaction, string saleStamp, string employeeName)
        {
            if (employeeName == "")
                ClearEmployee(sqlTransaction, saleStamp);
            else
                SetEmployee(sqlTransaction, saleStamp, employeeName);
        }

        public static void CloseSale(SqlTransaction sqlTransaction, string saleStamp)
        {
            const string query =
                @"  update	fi
	                set		stipo = 2
	                where	ftstamp = @SaleStamp";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, new SqlParameter("SaleStamp", saleStamp));          
        }

        public static ListagemZDataSet GetListagemZ(SqlTransaction sqlTransaction, DateTime day, string shop)
        {
            ListagemZDataSet dataSet = new ListagemZDataSet();
            double paidValue = GetPaidValue(sqlTransaction, day, shop);
            double changeValue = GetChangeValue(sqlTransaction, day, shop);
            double checkValue = GetCheckValue(sqlTransaction, day, shop);
            double multibankValue = GetMultiBankValue(sqlTransaction, day, shop);
            int clients = GetClients(sqlTransaction, day, shop);
            dataSet.Base.AddBaseRow(shop, "", "", "", "", day, day, paidValue, changeValue, checkValue,multibankValue, clients);
            GetMovments(sqlTransaction, day, shop, dataSet.Familia);
            SetLocalVendaListagemZ(dataSet);
            return dataSet;   
        }

        private static void SetLocalVendaListagemZ(ListagemZDataSet dataSet)
        {
            if (!dataSet.Base[0].IsLocalVendaNull() && dataSet.Base[0].LocalVenda != "")
                return;
            if(dataSet.Familia.Count == 0)
                return;
            string localVenda = dataSet.Familia[0].LocalVenda;
            foreach (ListagemZDataSet.FamiliaRow family in dataSet.Familia)
                if(family.LocalVenda != localVenda)
                    return;
            dataSet.Base[0].LocalVenda = localVenda;
        }


        public static string GetDocumentTypeName(SqlTransaction sqlTransaction, string shop)
        {
            const string query =
                @"  select  postd.nmdoc
                    from    postd, pos
                    where   pos.site = @Shop 
                            and postd.posstamp = pos.posstamp";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("Shop", shop));
        }

    }
}
